package com.example.music.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "music_player.db";
    private static final int DATABASE_VERSION = 1;

    // 最近播放表
    public static final String TABLE_RECENT_PLAY = "recent_play";
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_ITEM_ID = "item_id";
    public static final String COLUMN_TYPE = "type"; // 0:歌曲, 1:歌单, 2:专辑
    public static final String COLUMN_PLAY_TIME = "play_time";

    // 收藏表
    public static final String TABLE_FAVORITES = "favorites";
    public static final String COLUMN_SONG_PATH = "song_path";
    public static final String COLUMN_ADD_TIME = "add_time";

    // 歌单表
    public static final String TABLE_PLAYLISTS = "playlists";
    public static final String COLUMN_PLAYLIST_ID = "playlist_id";
    public static final String COLUMN_PLAYLIST_NAME = "name";
    public static final String COLUMN_CREATE_TIME = "create_time";

    // 歌单-歌曲关联表
    public static final String TABLE_PLAYLIST_SONGS = "playlist_songs";
    public static final String COLUMN_PLAYLIST_REF = "playlist_id";
    public static final String COLUMN_SONG_REF = "song_path";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建最近播放表
        String createRecentTable = "CREATE TABLE " + TABLE_RECENT_PLAY + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_ITEM_ID + " TEXT NOT NULL,"
                + COLUMN_TYPE + " INTEGER NOT NULL,"
                + COLUMN_PLAY_TIME + " INTEGER NOT NULL)";
        db.execSQL(createRecentTable);

        // 创建收藏表
        String createFavoritesTable = "CREATE TABLE " + TABLE_FAVORITES + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_SONG_PATH + " TEXT NOT NULL UNIQUE,"
                + COLUMN_ADD_TIME + " INTEGER NOT NULL)";
        db.execSQL(createFavoritesTable);

        // 创建歌单表
        String createPlaylistsTable = "CREATE TABLE " + TABLE_PLAYLISTS + "("
                + COLUMN_PLAYLIST_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_PLAYLIST_NAME + " TEXT NOT NULL UNIQUE,"
                + COLUMN_CREATE_TIME + " INTEGER NOT NULL)";
        db.execSQL(createPlaylistsTable);

        // 创建歌单-歌曲关联表
        String createPlaylistSongsTable = "CREATE TABLE " + TABLE_PLAYLIST_SONGS + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_PLAYLIST_REF + " INTEGER NOT NULL,"
                + COLUMN_SONG_REF + " TEXT NOT NULL,"
                + "FOREIGN KEY(" + COLUMN_PLAYLIST_REF + ") REFERENCES "
                + TABLE_PLAYLISTS + "(" + COLUMN_PLAYLIST_ID + ") ON DELETE CASCADE,"
                + "UNIQUE(" + COLUMN_PLAYLIST_REF + ", " + COLUMN_SONG_REF + "))";
        db.execSQL(createPlaylistSongsTable);

        // 启用外键支持
        db.execSQL("PRAGMA foreign_keys = ON;");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECENT_PLAY);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_FAVORITES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLAYLISTS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLAYLIST_SONGS);
        onCreate(db);
    }
}